﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;

namespace StudentManagementSystem
{
    public class School
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public List<Class> Classes { get; set; }

        public School(int id, string name)
        {
            Id = id;
            Name = name;
            Classes = new List<Class>();
        }
    }

    public class Class
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public List<Student> Students { get; set; }

        public Class(int id, string name)
        {
            Id = id;
            Name = name;
            Students = new List<Student>();
        }
    }

    public class Student
    {
        public int Id { get; set; }
        public string Name { get; set; }

        public Student(int id, string name)
        {
            Id = id;
            Name = name;
        }
    }

    public static class StudentManager
    {
        private static SQLiteConnection connection;

        public static void InitializeDatabase()
        {
            connection = new SQLiteConnection("Data Source=:memory:");
            connection.Open();

            // 创建学校表
            string createSchoolTableQuery = "CREATE TABLE School (Id INTEGER PRIMARY KEY, Name TEXT)";
            ExecuteNonQuery(createSchoolTableQuery);

            // 创建班级表
            string createClassTableQuery = "CREATE TABLE Class (Id INTEGER PRIMARY KEY, Name TEXT, SchoolId INTEGER)";
            ExecuteNonQuery(createClassTableQuery);

            // 创建学生表
            string createStudentTableQuery = "CREATE TABLE Student (Id INTEGER PRIMARY KEY, Name TEXT, ClassId INTEGER)";
            ExecuteNonQuery(createStudentTableQuery);

            // 创建日志表
            string createLogTableQuery = "CREATE TABLE Log (Id INTEGER PRIMARY KEY, Description TEXT)";
            ExecuteNonQuery(createLogTableQuery);
        }

        public static void AddSchool(School school)
        {
            string insertSchoolQuery = $"INSERT INTO School (Id, Name) VALUES ({school.Id}, '{school.Name}')";
            ExecuteNonQuery(insertSchoolQuery);
        }

        public static void AddClass(Class classObj, int schoolId)
        {
            string insertClassQuery = $"INSERT INTO Class (Id, Name, SchoolId) VALUES ({classObj.Id}, '{classObj.Name}', {schoolId})";
            ExecuteNonQuery(insertClassQuery);
        }

        public static void AddStudent(Student student, int classId)
        {
            string insertStudentQuery = $"INSERT INTO Student (Id, Name, ClassId) VALUES ({student.Id}, '{student.Name}', {classId})";
            ExecuteNonQuery(insertStudentQuery);
        }

        public static List<School> GetAllSchools()
        {
            List<School> schools = new List<School>();

            string selectSchoolsQuery = "SELECT * FROM School";
            DataTable schoolsTable = ExecuteQuery(selectSchoolsQuery);

            foreach (DataRow row in schoolsTable.Rows)
            {
                int id = Convert.ToInt32(row["Id"]);
                string name = row["Name"].ToString();

                School school = new School(id, name);
                schools.Add(school);
            }

            return schools;
        }

        public static List<Class> GetClassesBySchool(int schoolId)
        {
            List<Class> classes = new List<Class>();

            string selectClassesQuery = $"SELECT * FROM Class WHERE SchoolId = {schoolId}";
            DataTable classesTable = ExecuteQuery(selectClassesQuery);

            foreach (DataRow row in classesTable.Rows)
            {
                int id = Convert.ToInt32(row["Id"]);
                string name = row["Name"].ToString();

                Class classObj = new Class(id, name);
                classes.Add(classObj);
            }

            return classes;
        }

        public static List<Student> GetStudentsByClass(int classId)
        {
            List<Student> students = new List<Student>();

            string selectStudentsQuery = $"SELECT * FROM Student WHERE ClassId = {classId}";
            DataTable studentsTable = ExecuteQuery(selectStudentsQuery);

            foreach (DataRow row in studentsTable.Rows)
            {
                int id = Convert.ToInt32(row["Id"]);
                string name = row["Name"].ToString();

                Student student = new Student(id, name);
                students.Add(student);
            }

            return students;
        }

        public static void LogAction(string description)
        {
            string insertLogQuery = $"INSERT INTO Log (Description) VALUES ('{description}')";
            ExecuteNonQuery(insertLogQuery);
        }

        public static DataTable ExecuteQuery(string query)
        {
            SQLiteDataAdapter adapter = new SQLiteDataAdapter(query, connection);
            DataTable dataTable = new DataTable();
            adapter.Fill(dataTable);
            return dataTable;
        }

        public static void ExecuteNonQuery(string query)
        {
            SQLiteCommand command = new SQLiteCommand(query, connection);
            command.ExecuteNonQuery();
        }
    }

    public class Program
    {
        public static void Main(string[] args)
        {
            StudentManager.InitializeDatabase();

            // 创建学校实例
            School school = new School(1, "School");

            // 添加学校
            StudentManager.AddSchool(school);

            // 生成更多的数据示例
            GenerateSampleData();

            // 获取所有学校
            List<School> allSchools = StudentManager.GetAllSchools();
            foreach (School schoolObj in allSchools)
            {
                Console.WriteLine($"School ID: {schoolObj.Id}, Name: {schoolObj.Name}");
            }

            Console.WriteLine();

            // 获取指定学校的班级
            int schoolId = 1; // 选择学校1
            List<Class> classes = StudentManager.GetClassesBySchool(schoolId);
            foreach (Class classObj in classes)
            {
                Console.WriteLine($"Class ID: {classObj.Id}, Name: {classObj.Name}");
            }

            Console.WriteLine();

            // 获取指定班级的学生
            int classId = 1; // 选择班级1
            List<Student> students = StudentManager.GetStudentsByClass(classId);
            foreach (Student student in students)
            {
                Console.WriteLine($"Student ID: {student.Id}, Name: {student.Name}");
            }

            Console.WriteLine();

            // 查询和显示日志
            string selectLogsQuery = "SELECT * FROM Log";
            DataTable logsTable = StudentManager.ExecuteQuery(selectLogsQuery);

            foreach (DataRow row in logsTable.Rows)
            {
                int logId = Convert.ToInt32(row["Id"]);
                string description = row["Description"].ToString();

                Console.WriteLine($"Log ID: {logId}, Description: {description}");
            }

            Console.ReadLine();
        }

        // 生成更多的数据示例
        public static void GenerateSampleData()
        {
            Random random = new Random();

            // 获取已有学校信息
            List<School> allSchools = StudentManager.GetAllSchools();

            // 为每个学校生成班级和学生数据
            foreach (School school in allSchools)
            {
                // 生成3个班级
                for (int classId = 1; classId <= 3; classId++)
                {
                    string className = $"Class {classId}";
                    Class classObj = new Class(classId, className);
                    // 添加班级，并确保班级属于指定的学校
                    StudentManager.AddClass(classObj, school.Id);

                    // 为每个班级生成5个学生
                    for (int studentId = 1; studentId <= 5; studentId++)
                    {
                        string studentName = $"Student {studentId}";
                        // 生成一个唯一的学生ID
                        int uniqueStudentId = random.Next(1000, 10000);
                        Student student = new Student(uniqueStudentId, studentName);
                        // 添加学生，并确保学生属于指定的班级
                        StudentManager.AddStudent(student, classObj.Id);
                    }
                }
            }
        }
    }
}
